package com.edu.app.mapper.linkmappers;

import com.edu.app.bean.core.SysActivity;
import com.edu.app.bean.core.SysUser;
import com.edu.app.bean.linkbeans.SysUserActivity;
import com.edu.app.bean.virtualbean.UserActByDate;
import com.edu.app.bean.virtualbean.UserActWx;
import com.edu.app.util.AppBaseMapper;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public interface SysUserActivityMapper extends AppBaseMapper<SysUserActivity> {
    @Select("SELECT t3.* FROM S_USER t1 INNER JOIN S_USER_ACTIVITY t2 on t1.ID = t2.USER_ID INNER " +
            "JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "WHERE t1.id = #{id}")
    List<SysActivity> getActivityByUserId(Integer id);

    @Select("SELECT t1.* FROM S_USER t1 INNER JOIN S_USER_ACTIVITY t2 on t1.ID = t2.USER_ID INNER " +
            "JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "WHERE t3.id = #{id}")
    List<SysUser> getUserByActivityId(Integer id);

    @Select("SELECT DISTINCT DATE_FORMAT(t3.time,'%Y-%m-%d') as time FROM S_USER t1 INNER JOIN S_USER_ACTIVITY t2 on t1.ID = t2.USER_ID " +
            "INNER JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "WHERE t1.username = #{username} and t2.roleplay = #{rolePlay} order by time asc")
    List<String> getDateHasActivity(@Param("username") String username, @Param("rolePlay") String rolePlay);

    @Select("SELECT t3.* FROM S_USER t1 INNER JOIN S_USER_ACTIVITY t2 on t1.ID = t2.USER_ID INNER " +
            "JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "WHERE t1.username = #{username} and Date(t3.time) = #{date}")
    List<SysActivity> getActivityByUserName(@Param("username") String username,@Param("date") String date);

    //注：传参数到Mapper层，当参数超过两个以上的时候，注意使用@Param标注
    @Select("SELECT * FROM S_USER_ACTIVITY WHERE user_id = #{user_id} and activity_id = #{activity_id}")
    SysUserActivity getUserActivity(@Param("user_id") Integer user_id, @Param("activity_id") Integer activity_id);

    @Select("SELECT t3.* FROM S_USER t1 INNER JOIN S_USER_ACTIVITY t2 on t1.ID = t2.USER_ID INNER " +
            "JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "WHERE t1.username = #{username} and t3.time > Now() ORDER BY t3.time DESC")
    List<SysActivity> getUserActivityByPage(String username);

    //get pending activity number
    @Select("SELECT COUNT(*) FROM (SELECT t3.*,t4.type FROM S_USER t1 INNER JOIN S_USER_ACTIVITY t2 on t1.ID = t2.USER_ID " +
            "INNER JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "INNER JOIN S_LIST_OF_VALUE t4 on t4.id = t3.type_id " +
            "WHERE t1.username = #{username} and t4.type like '%${type}%' and t2.roleplay = #{rolePlay} and t3.time < Now() and t3.status = 0) AS tbl")
    Integer getUserPendingActivityNumber(@Param("username") String username, @Param("type") String type, @Param("rolePlay") String rolePlay);

    //status = 0 is the default value for pending activities
    @Select("SELECT t3.* FROM S_USER t1 INNER JOIN S_USER_ACTIVITY t2 on t1.ID = t2.USER_ID INNER " +
            "JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "WHERE t1.username = #{username} and t3.time < Now() and t3.status = 0 ORDER BY t3.time DESC")
    List<SysActivity> getUserPendingActivityByPage(String username);

    //status = 1 is the default value for feedback pending activities
    @Select("SELECT t3.* FROM S_USER t1 INNER JOIN S_USER_ACTIVITY t2 on t1.ID = t2.USER_ID INNER " +
            "JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "WHERE t1.username = #{username} and t3.time < Now() and t3.status = 1 ORDER BY t3.time DESC")
    List<SysActivity> getUserPendingFeedbackByPage(String username);

    //status = 2 is the activities which are completed successfully
    @Select("SELECT t3.* FROM S_USER t1 INNER JOIN S_USER_ACTIVITY t2 on t1.ID = t2.USER_ID INNER " +
            "JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "WHERE t1.username = #{username} and t3.time < Now() and t3.status = 2 ORDER BY t3.time DESC")
    List<SysActivity> getUserCompletedActivityByPage(String username);

    @Select("DELETE FROM S_USER_ACTIVITY WHERE activity_id = #{activity_id}")
    Integer deleteUserActivityByActivityId(Integer activity_id);

    @Select("DELETE FROM S_USER_ACTIVITY WHERE user_id = #{user_id}")
    Integer deleteUserActivityByUserId(Integer user_id);

    @Select("select DISTINCT DATE_FORMAT(t1.time,'%Y-%m-%d %H:%i') as time,t1.id as activity_id, t1.title,t1.content,t3.id as user_id, " +
            "t3.nickname, group_concat(t7.name) as name, t8.value as activity_type from s_activity t1 " +
            "inner join s_user_activity t2 on t1.id = t2.activity_id " +
            "inner join s_user t3 on t3.id = t2.user_id " +
            "inner join s_user_role t4 on t4.user_id = t3.id " +
            "inner join s_role t5 on t4.role_id = t5.id " +
            "inner join s_customer_activity t6 on t1.id = t6.activity_id " +
            "inner join s_customer t7 on t6.customer_id = t7.id " +
            "inner join s_list_of_value t8 on t8.id = t1.type_id " +
            "where t3.username=#{username} and t1.time > now() and t5.role_name = #{role} and t2.roleplay=#{role} " +
            "and t8.type like #{type} group by time,activity_id,title,content,user_id,nickname order by time asc")
    List<UserActByDate> getInComingActivityModel(@Param("username") String username, @Param("role") String role, @Param("type") String type);

    @Select("select DISTINCT DATE_FORMAT(t1.time,'%Y-%m-%d %H:%i') as time,t1.id as activity_id, t1.title,t1.content,t3.id as user_id, " +
            "t3.nickname, group_concat(t7.name) as name, t8.value as activity_type from s_activity t1 " +
            "inner join s_user_activity t2 on t1.id = t2.activity_id " +
            "inner join s_user t3 on t3.id = t2.user_id " +
            "inner join s_user_role t4 on t4.user_id = t3.id " +
            "inner join s_role t5 on t4.role_id = t5.id " +
            "inner join s_customer_activity t6 on t1.id = t6.activity_id " +
            "inner join s_customer t7 on t6.customer_id = t7.id " +
            "inner join s_list_of_value t8 on t8.id = t1.type_id " +
            "where t3.username=#{username} and t8.type like #{type} and t2.roleplay=#{role} and t1.status = #{status} and time <= now() and t5.role_name = #{role} " +
            "group by time,activity_id,title,content,user_id,nickname order by time ${seq}")
    List<UserActByDate> getActivityModelByStatus(@Param("username") String username, @Param("status") Integer status, @Param("role") String role, @Param("type") String type, @Param("seq") String seq);

    //小于15分钟的事件
    @Select("select id,username,nickname,company,wx_open_id,value as lesson,group_concat(name separator ',') as customer, time from " +
            "(select distinct t3.id,t1.username,t1.nickname,t7.company,t1.wx_open_id,t4.value, t6.name, t3.time from s_user t1 " +
            "inner join s_user_activity t2 on t1.id = t2.user_id " +
            "inner join s_activity t3 on t3.id = t2.activity_id " +
            "inner join s_list_of_value t4 on t3.type_id = t4.id " +
            "inner join s_customer_activity t5 on t5.activity_id = t3.id " +
            "inner join s_customer t6 on t6.id = t5.customer_id " +
            "inner join s_tenant t7 on t1.tenant_id = t7.id " +
            "where t4.type = 'S_LOV_LESSON' and t1.wx_open_id is not null and t3.wx_flag = '0' " +
            "and TIMESTAMPDIFF(MINUTE, t3.time, now()) < 15) tb " +
            "group by id,username,nickname,company,wx_open_id,lesson,time")
    List<UserActWx> getUserActWx();
}
